﻿using System;
using System.Collections;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace DMirror.SQLite
{
    public class DMirror
    {
        public string sc;

        public bool debugMode;
        private Hashtable sites;//<key>site_id,<value>site_name
        private Hashtable taglist;// <key>tagname=dname,tag <value>tagid

        /*Constructor*/
        public void DMirror(string BasePath)
        {

        }


        /*

        public DMirror(string sDBName, string sUserName, string sUserPWD, string sDBServer)//connect to db
        {
            MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder();
            sb.Database = sDBName;
            sb.Server = sDBServer;
            sb.Port = 3306;
            sb.UserID = sUserName;
            sb.Password = sUserPWD;
            sc = sb.ConnectionString;
            con = new MySqlConnection(sc);
            con.Open();
        }
         */
        /*common*/
        public void getData(string sql, ref DataTable d)
        {
            //d.Dispose();
            d = new DataTable();
            MySqlDataAdapter a = new MySqlDataAdapter(sql, con);
            a.Fill(d);
        }

        /*sites*/
        public DataTable initSites()
        {
            DataTable dlist = new DataTable();
            sites = new Hashtable();

            getData("select site_id,site_url,site_name from sites", ref dlist);

            foreach (DataRow item in dlist.Rows)
            {
                sites.Add(item[0].ToString(), item[1].ToString());
            }
            return dlist;
        }
        public string getSiteURL(string site_id)
        {
            if (sites.ContainsKey(site_id))
            {
                return (string)sites[site_id];
            }
            else
                return "-1";
        }
        /*posts*/
        /// <summary>
        /// 获取Post信息
        /// </summary>
        /// <param name="post_id">post 标志</param>
        /// <param name="site_id">site 标志</param>
        /// <returns></returns>
        public DPost getPostInfo(int post_id, int site_id)
        {
            DPost d = new DPost();
            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter(string.Format("select * from posts_{1} where post_id={0} ", post_id.ToString(), site_id.ToString()), con);
            da.Fill(dt);
            DataRow r = dt.Rows[0];
            Util.fillRowToDPost(ref r, ref d);
            r = null;
            dt = null;
            return d;
        }
        /// <summary>
        /// 批量同步Post状态
        /// </summary>
        /// <param name="site_id"></param>
        /// <param name="fileInfo"></param>
        public void bulkSyncPosts(int site_id, ref Hashtable fileInfo)
        {
            MySqlCommand cmd = new MySqlCommand("truncate table sync_temp", con);
            cmd.ExecuteNonQuery();
            MySqlTransaction ta = con.BeginTransaction();
            cmd = new MySqlCommand("", con, ta);
            /*insert all data into temp_table */
            foreach (string k in fileInfo.Keys)
            {
                cmd.CommandText = string.Format(@"
insert into sync_temp (post_id,size) select'{0}','{1}' from dual where not exists(select 1 from sync_temp where post_id='{0}')", k, (string)fileInfo[k]);
                cmd.ExecuteNonQuery();
            }
            ta.Commit();
            //正常部分
            cmd = new MySqlCommand(@"
update posts a set a.status='1',modified=null 
where a.status='0' 
and exists(select 1 from sync_temp b where b.post_id=a.post_id and b.size=a.size)", con);
            cmd.ExecuteNonQuery();
            //缺失部分
            cmd = new MySqlCommand(@"
update posts a set a.status='-1',modified=null 
where a.status>1 
and not exists(select 1 from sync_temp b where b.post_id=a.post_id)", con);
            cmd.ExecuteNonQuery();

            //不匹配部分
            cmd = new MySqlCommand(@"
update posts a set a.status='-2',modified=null 
where exists(select 1 from sync_temp b where b.post_id=a.post_id and a.size <> b.size)", con);
            cmd.ExecuteNonQuery();
            cmd = null;


        }

        public void getPost(int site_id, ref DataTable results, string status)
        {
            MySqlDataAdapter da = new MySqlDataAdapter(string.Format(@"select * from posts_{0} where status in ({1})", site_id.ToString(), status), con);
            da.Fill(results);
            da = null;
        }

        /// <summary>
        /// 单独添加Post
        /// </summary>
        /// <param name="newpost"></param>
        public void addPost(DPost newpost)
        {

            MySqlCommand cmd = new MySqlCommand("", con);
            cmd.CommandText = string.Format(@"
insert IGNORE into posts_{1}  (post_id,md5,height,width,url,url_thumb,status,size,modified) 
values ('{0}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',now()) 
", new string[] {
            newpost.PostID.ToString(),
            newpost.SiteID.ToString(),
            newpost.MD5,
            newpost.Height.ToString(),
            newpost.Width.ToString(),
            newpost.URL,
            newpost.tURL,
            newpost.Status.ToString(),
            newpost.Size.ToString()
            });

            /*
                        cmd.CommandText = string.Format(@"
            insert into posts_{1} (post_id,md5,height,width,url,url_thumb,status,size,modified) 
            select '{0}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',null from dual
            where not exists(select 1 from posts where post_id='{0}') ", new string[]{
                        newpost.PostID.ToString(),
                        newpost.SiteID.ToString(),
                        newpost.MD5,
                        newpost.Height.ToString(),
                        newpost.Width.ToString(),
                        newpost.URL,
                        newpost.tURL,
                        newpost.Status.ToString(),
                        newpost.Size.ToString()
                       });*/
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                errorLog("newPost", cmd.CommandText, exp.Message);
            }
            cmd = null;
        }
        /// <summary>
        /// 批量添加Post
        /// </summary>
        /// <param name="newpost"></param>
        public void addPost(ref DPost[] newpost)
        {
            MySqlTransaction ta = con.BeginTransaction();
            MySqlCommand cmd = new MySqlCommand("", con, ta);
            for (int i = 0; i < newpost.Length; i++)
            {
                if (newpost[i] == null || newpost[i].MD5 == "")
                    break;
                /*                cmd.CommandText =  string.Format(@"insert into posts_{1} 
                                (post_id,md5,height,width,url,url_thumb,status,modified) values({0},{2},{3},{4},{5},{6},{7},now()) if not exists (select 1 from posts where post_id='{0}')", new string[]{
                            newpost[i].PostID.ToString(),
                            newpost[i].SiteID.ToString(),
                            newpost[i].MD5,
                            newpost[i].Height.ToString(),
                            newpost[i].Width.ToString(),
                            newpost[i].URL,
                            newpost[i].tURL,
                            newpost[i].Status.ToString()});*/

                cmd.CommandText = string.Format(@"insert IGNORE into posts_{1} 
                (post_id,md5,height,width,url,url_thumb,status,modified) values({0},{2},{3},{4},{5},{6},{7},now())
                 ", new string[] {
                newpost[i].PostID.ToString(),
                newpost[i].SiteID.ToString(),
                newpost[i].MD5,
                newpost[i].Height.ToString(),
                newpost[i].Width.ToString(),
                newpost[i].URL,
                newpost[i].tURL,
                newpost[i].Status.ToString(),
                newpost[i].Size.ToString() });
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    errorLog("newPost", cmd.CommandText, exp.Message);
                }
            }
            ta.Commit();
            ta = null;
            cmd = null;
        }


        /*Tags */
        /// <summary>
        /// 初始化Tag缓冲
        /// </summary>
        public void initTagList()
        {
            taglist = new Hashtable();
            taglist.Clear();
            MySqlDataAdapter a = new MySqlDataAdapter(@"
select tag_id,tag_name from tagList union all
select tag_id,tag_name from tagLocale", con);
            DataTable dt = new DataTable();
            a.Fill(dt);
            a = null;
            foreach (DataRow r in dt.Rows)
            {
                taglist.Add(r["tag_name"].ToString(), r["tag_id"].ToString());
            }
            dt = null;

        }
        /// <summary>
        /// 根据Tag名称获取TagID
        /// </summary>
        /// <param name="tagName">tag名称</param>
        /// <returns>tagID</returns>
        public int
        getTagID(string otagName)
        {
            int id = -1;
            string tagName = otagName.Replace("\\", "~").Replace("\"", "\"\"");

            if (taglist.ContainsKey(tagName))
            {
                int.TryParse((string)taglist[tagName], out id);
            }
            return id;
        }

        /*

                /// <summary>
                /// 向TagList中新增一个Tag
                /// </summary>
                /// <param name="tagName">DanbooruTag名称</param>
                /// <param name="locale">本地化名称</param>
                /// <param name="localName">本地化语言</param>
                /// <returns>Tag标志</returns>
                public int insertTag(string tagName, string localName, string locale)
                {
                    int tagId = -1;
                    tagId = getTagID(tagName);
                    if (tagId == -1)//DanbooruTag不存在,添加新locale=raw的tag
                    {
                        MySqlCommand cmd = new MySqlCommand(string.Format(
        @"insert into tagList (tag_name,modified) values ('{0}','{1}')",
        tagName, Util.ToTimeStamp(DateTime.Now)), con);
                        try
                        {
                            int r = cmd.ExecuteNonQuery();
                            if (debugMode) errorLog("newTag", cmd.CommandText, r.ToString());
                            initTagList();
                            insertTag(tagName, localName, locale);
                        }
                        catch (Exception e)
                        {
                            errorLog("newTag", cmd.CommandText, e.Message);
                        }
                    }
                    else
                    {
                        MySqlCommand cmd = new MySqlCommand(string.Format(
        @"insert into tagLocale (tag_id,tag_name,locale,modified) values ('{0}','{1}','{2}','{3}')",new string[]{
        tagId.ToString(),localName,locale, Util.ToTimeStamp(DateTime.Now)}), con);
                        try
                        {
                            int r = cmd.ExecuteNonQuery();
                            if (debugMode) errorLog("localeTag", cmd.CommandText, r.ToString());
                        }
                        catch (Exception e)
                        {
                            errorLog("localeTag", cmd.CommandText, e.Message);
                        }
                    }




                    return tagId;
                }*/
        /// <summary>
        /// 添加新Tag
        /// </summary>
        /// <param name="tagName">Danbooru Tag</param>
        /// <returns></returns>
        public int newTag(string otagName)
        {
            int tagId = -1;
            // string tagName = otagName.Replace("'", @"\'");
            string tagName = otagName.Replace("\\", "~").Replace("\"", "\"\"");
            tagId = getTagID(tagName);
            if (tagId == -1)//DanbooruTag不存在,添加新locale=raw的tag
            {
                /*MySqlCommand cmd = new MySqlCommand(string.Format(
                @"insert into  taglist(tag_name,modified)
                select '{0}',null from dual
                where not exists(select * from taglist where tag_name = '{0}')",
                tagName), con);*/

                MySqlCommand cmd = new MySqlCommand(string.Format(
                "insert IGNORE into  taglist(tag_name,modified) values(\"{0}\",now())", tagName), con);
                int r = cmd.ExecuteNonQuery();
                //         if (debugMode) errorLog("newTag", cmd.CommandText, r.ToString());
                //initTagList();
                MySqlDataAdapter da = new MySqlDataAdapter(string.Format("select tag_id from taglist where tag_name = \"{0}\"", tagName), con);
                DataTable dt = new DataTable();
                da.Fill(dt);
                taglist.Add(tagName, dt.Rows[0]["tag_id"].ToString());
                return getTagID(tagName);

            }
            return tagId;
        }
        /// <summary>
        /// 批量添加Tags
        /// </summary>
        /// <param name="tags">String数组，需要添加的tag</param>
        public void newTags(string[] tags)
        {

            string scmd = "insert IGNORE into  taglist(tag_name,modified) values(\"{0}\",now())";
            MySqlTransaction ta = con.BeginTransaction();
            MySqlCommand cmd = new MySqlCommand("", con, ta);
            try
            {
                //insert every tag
                for (int i = 0; i < tags.Length; i++)
                {
                    cmd.CommandText = string.Format(scmd, tags[i]);
                    cmd.ExecuteNonQuery();
                }
                //flush cache
                initTagList();
            }
            catch (Exception ex)
            {
                errorLog("bulkNewTags", cmd.CommandText, ex.Message);
            }
            finally
            {
                ta.Commit();
            }
            ta = null;
            cmd = null;

        }

        public void updateTagLocale(int tagID, ref Hashtable localeList)
        {



        }

        public void cleanTag(int post_id, int site_id)
        {
            MySqlCommand cmd = new MySqlCommand(string.Format(@"
delete from  tags_{1} where  post_id='{0}'
", post_id.ToString(), site_id.ToString()), con);
            // errorLog("debug:insert_tag", cmd.CommandText, "");
            cmd.ExecuteNonQuery();
            cmd = null;

        }
        /// <summary>
        /// 设置tag
        /// </summary>
        /// <param name="post_id"></param>
        /// <param name="site_id"></param>
        /// <param name="tag_id"></param>
        public void assignTag(int post_id, int site_id, int tag_id)
        {
            /*MySqlCommand cmd = new MySqlCommand(string.Format(@"
            insert into tags (post_id,tag_id,site_id,modified)
            select '{0}','{1}','{2}' ,now() from dual
            where not exists(select * from tags where site_id='{2}' and post_id='{0}' and tag_id='{1}')
            ", post_id.ToString(), tag_id.ToString(), site_id.ToString()), con);*/
            MySqlCommand cmd = new MySqlCommand(string.Format(@"
insert IGNORE into tags_{2} (post_id,tag_id,modified)
values('{0}','{1}',now()) 
", post_id.ToString(), tag_id.ToString(), site_id.ToString()), con);
            // errorLog("debug:insert_tag", cmd.CommandText, "");
            cmd.ExecuteNonQuery();
            cmd = null;

        }
        public int assignTag(int post_id, int site_id, string tag_name)
        {
            int tag_id = getTagID(tag_name);
            if (tag_id == -1)
            {
                tag_id = newTag(tag_name);
                return 1;
            }
            assignTag(post_id, site_id, tag_id);
            return -1;
        }

        /// <summary>
        /// 批量增加
        /// </summary>
        /// <param name="tagName">存有tagName的string数组</param>
        public void bulkInsertTags(string[] tagName)
        {



        }

        /// <summary>
        /// 记录错误与日志
        /// </summary>
        /// <param name="method">操作</param>
        /// <param name="command">命令</param>
        /// <param name="result">结果</param>
        public void errorLog(string method, string command, string result)
        {
            //    MySqlTransaction ta = con.BeginTransaction();
            //  MySqlCommand cmd = new MySqlCommand(string.Format("insert into logs (modified,method,command,result) values(null,'{0}','{1}','{2}')",new string[]{method,command,result}),con,ta);
            // cmd.ExecuteNonQuery();
            // ta.Commit();
            // cmd = null;
        }
    }

}
